﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Activity.Data;
using Activity.Helper;
using Nancy.Extensions;
using Newtonsoft.Json;

namespace Activity.API
{
    public class ShopGroupAndTargetHandler
    {
        private string COMPANYID = "KM";
        public IList<ShopGroupAndTargetModel.ConditionsModel> GetConditions()
        {
            try
            {
                var db = new ActivityEntities();
                var query = (from p in db.KM_ShopGroupAndTarget
                             where p.CompanyID == COMPANYID
                             group new { p.ProvincesName, p.RegionalManager, p.ShopManager } by p.OperationAreaName
                    into temp
                             select new ShopGroupAndTargetModel.ConditionsModel
                             {
                                 OperationAreaName = temp.Key,
                                 provincesList = temp.Select(d => new ShopGroupAndTargetModel.ProvincesModel
                                 {
                                     ProvincesName = d.ProvincesName,
                                 }).ToList(),
                                 regionalManagerList = temp.Select(d => new ShopGroupAndTargetModel.RegionalManagerModel
                                 {
                                     RegionalManager = d.RegionalManager,
                                 }).ToList(),
                                 shopManagerList = temp.Select(d => new ShopGroupAndTargetModel.ShopManagerModel
                                 {
                                     ShopManager = d.ShopManager,
                                 }).ToList(),
                             }).ToList();
                query.ForEach(d =>
                {
                    d.provincesList = d.provincesList.DistinctBy(r => r.ProvincesName).ToList();
                    d.regionalManagerList = d.regionalManagerList.Where(r => !string.IsNullOrEmpty(r.RegionalManager))
                        .DistinctBy(r => r.RegionalManager).ToList();
                    d.shopManagerList = d.shopManagerList.Where(r => !string.IsNullOrEmpty(r.ShopManager))
                        .DistinctBy(r => r.ShopManager).ToList();
                });
                return query;
            }
            catch (Exception ex)
            {
                Log.Error(ex);
                return null;
            }
        }

        public GridDatas GetShopGroupAndTarget(string OperationAreaName, string ProvincesName,
            string RegionalManager, string ShopManager, string ShopStr, int Rows, int Page)
        {
            try
            {
                var count = GetShopGroupAndTargetCount(OperationAreaName, ProvincesName, RegionalManager, ShopManager,
                    ShopStr);
                var list = GetShopGroupAndTargetList(OperationAreaName, ProvincesName, RegionalManager, ShopManager,
                    ShopStr, Rows, Page);
                var result = new GridDatas
                {
                    total = count.ToString(),
                    rows = list,
                    pageindex = Page.ToString(),
                    pagecount = (Math.Ceiling(count / (1.0 * Rows))).ToString(),
                };
                return result;
            }
            catch (Exception ex)
            {
                Log.Error(ex);
                return null;
            }
        }

        public IList<KM_ShopGroupAndTarget> GetShopGroupAndTargetList(string OperationAreaName, string ProvincesName,
            string RegionalManager, string ShopManager, string ShopStr, int Rows, int Page)
        {
            try
            {
                var db = new ActivityEntities();
                var list = db.KM_ShopGroupAndTarget
                    .Where(d => d.CompanyID == COMPANYID &&
                                (string.IsNullOrEmpty(OperationAreaName) || d.OperationAreaName == OperationAreaName) &&
                                (string.IsNullOrEmpty(ProvincesName) || d.ProvincesName == ProvincesName) &&
                                (string.IsNullOrEmpty(RegionalManager) || d.RegionalManager == RegionalManager) &&
                                (string.IsNullOrEmpty(ShopManager) || d.ShopManager == ShopManager) &&
                                (string.IsNullOrEmpty(ShopStr) || d.ShopCode.Contains(ShopStr) ||
                                 d.ShopName.Contains(ShopStr))).OrderBy(d => d.OperationAreaName)
                    .ThenBy(d => d.ProvincesName).ThenBy(d => d.RegionalManager).ThenBy(d => d.ShopManager)
                    .ThenBy(d => d.ShopCode);
                return list.Take(Rows * Page).Skip(Rows * (Page - 1)).ToList();
            }
            catch (Exception ex)
            {
                Log.Error(ex);
                return null;
            }
        }

        public int GetShopGroupAndTargetCount(string OperationAreaName, string ProvincesName,
            string RegionalManager, string ShopManager, string ShopStr)
        {
            try
            {
                var db = new ActivityEntities();
                var list = db.KM_ShopGroupAndTarget
                    .Where(d => d.CompanyID == COMPANYID &&
                                (string.IsNullOrEmpty(OperationAreaName) || d.OperationAreaName == OperationAreaName) &&
                                (string.IsNullOrEmpty(ProvincesName) || d.ProvincesName == ProvincesName) &&
                                (string.IsNullOrEmpty(RegionalManager) || d.RegionalManager == RegionalManager) &&
                                (string.IsNullOrEmpty(ShopManager) || d.ShopManager == ShopManager) &&
                                (string.IsNullOrEmpty(ShopStr) || d.ShopCode.Contains(ShopStr) ||
                                 d.ShopName.Contains(ShopStr))).OrderBy(d => d.OperationAreaName);
                return list.Count();
            }
            catch (Exception ex)
            {
                Log.Error(ex);
                return 0;
            }
        }

        public int ImportShopGroupAndTarget(string JsonData)
        {
            var db = new ActivityEntities();
            var tableName = "[dbo].[KM_ShopGroupAndTarget]";
            var tempTableName = $@"[dbo].[temp_{Guid.NewGuid():N}]";
            try
            {
                var list = JsonConvert.DeserializeObject<IList<KM_ShopGroupAndTarget>>(JsonData).ToList();
                list.ForEach(d => d.CompanyID = COMPANYID);
                var targetDateList = list.Select(r => r.TargetDate).ToList();
                var lastMonthStartDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month - 1, 1);
                var lastMonthEndDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).AddDays(-1);
                var oldList = db.KM_ShopGroupAndTarget
                    .Where(d => d.CompanyID == COMPANYID && (!targetDateList.Contains(d.TargetDate)) &&
                                d.TargetDate >= lastMonthStartDate && d.TargetDate <= lastMonthEndDate)
                    .Select(d => d).ToList();
                var newList = list;
                newList.AddRange(oldList);
                var query = from l in newList
                    join h in db.KM_YearHoliday
                    on new {l.CompanyID, l.TargetDate} equals new
                    {
                        h.CompanyID,
                        TargetDate = h.GregorianDay
                    }
                    into temp1
                    from tt in temp1.DefaultIfEmpty()
                    group l by new {tt?.TypeName,l.ShopCode}
                    into temp2
                    select temp2;
                foreach (var item in query)
                {
                    if (!string.IsNullOrEmpty(item.Key.TypeName))
                    {
                        foreach (var it in item)
                        {
                            it.HolidayTarget = item.Sum(d => d.DayTarget);
                        }
                    }
                }
                var monthGroup = query.SelectMany(d => d)
                    .GroupBy(d => new
                    {
                        d.TargetDate.Year,
                        d.TargetDate.Month,
                        d.ShopCode,
                    });
                foreach (var item in monthGroup)
                {
                    foreach (var it in item)
                    {
                        it.MonthTarget = item.Sum(d => d.DayTarget);
                        it.CreateTime = DateTime.Now;
                        if (it.RegionalManager == "-")
                        {
                            it.RegionalManager = null;
                        }
                        if (it.ShopManager == "-")
                        {
                            it.ShopManager = null;
                        }
                    }
                }
                var finalList = monthGroup.SelectMany(d => d)
                    .Where(d => d.TargetDate >= lastMonthStartDate).ToList();
                DropTable(db, tempTableName);
                CopyTable(db, tempTableName, tableName);
                ClearTable(db);
                BulkInsertExtension.BulkInsert((SqlConnection) db.Database.Connection, tableName, finalList);
                return 0;
            }
            catch (Exception ex)
            {
                ClearTable(db);
                CopyTable(db, tableName, tempTableName);
                Log.Error(ex);
                return -1;
            }
            finally
            {
                DropTable(db, tempTableName);
            }
        }

        public void ClearTable(ActivityEntities db)
        {
            try
            {
                const string Sql = @"DELETE FROM [dbo].[KM_ShopGroupAndTarget]";
                db.Database.ExecuteSqlCommand(Sql);
            }
            catch (Exception ex)
            {
                throw(ex);
            }
        }

        public void CopyTable(ActivityEntities db, string TableName1, string TableName2)
        {
            try
            {
                var Sql = $@"SELECT * INTO {TableName1} FROM {TableName2}";
                db.Database.ExecuteSqlCommand(Sql);
            }
            catch (Exception ex)
            {
                throw(ex);
            }
        }

        public void DropTable(ActivityEntities db,string TableName)
        {
            try
            {
                var Sql = $@"IF OBJECT_ID('{TableName}','U') IS NOT NULL  
DROP TABLE {TableName} ";
                db.Database.ExecuteSqlCommand(Sql);
            }
            catch (Exception ex)
            {
                throw(ex);
            }
        }
    }
}

public class ShopGroupAndTargetModel
{
    public class OperationAreaModel
    {
        public string OperationAreaName { get; set; }
    }

    public class ProvincesModel
    {
        public string ProvincesName { get; set; }
    }

    public class RegionalManagerModel
    {
        public string RegionalManager { get; set; }
    }

    public class ShopManagerModel
    {
        public string ShopManager { get; set; }
    }

    public class ConditionsModel : OperationAreaModel
    {
        public IList<ProvincesModel> provincesList { get; set; }
        public IList<RegionalManagerModel> regionalManagerList { get; set; }
        public IList<ShopManagerModel> shopManagerList { get; set; }
    }
}

public class GridDatas
{
    public string pageindex { set; get; }
    public string total { get; set; }
    public string pagecount { get; set; }

    public IList<KM_ShopGroupAndTarget> rows { get; set; }
}

